﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spInitializeInstallBatchScripts]
#-- Purpose:		Regenerates the batch files for deploying to the managed
#--					databases
#--	Last Update:	06/07/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spInitializeInstallBatchScripts]
(
	@report_type		varchar(20)			= 'Test',  --- Either Test, QA, or Production
	@rootDir			nvarchar(2000)		= '$(Deployment_Directory)'
)
AS

SET NOCOUNT OFF

DECLARE	@fileHeader nvarchar(MAX), @fileFooter nvarchar(MAX),
		@cmd nvarchar(2000), @server_name sysname, @file_name sysname, @is_first bit,
		@last_file_name sysname

SELECT	@is_first = 1,
		@cmd = 'call ' + @rootDir + 'code\$(DatabaseName)\Setup\deploy.cmd &&SERVERNAME&& | ' + @rootDir + 'tee.exe -a %FILENAME%',
		@fileHeader = '@ECHO OFF
REM Save to file ' + @rootDir + 'install_&&FILENAME&&.bat
SET Now=%Time: =0%
SET Today=%Date: =0%
SET DATESTR=%Today:~-4%%Today:~-10,2%%Today:~-7,2%
SET TIMESTR=%Now:~0,2%%Now:~3,2%%Now:~6,2%
SET FILENAME=' + @rootDir + 'logs\db_deploy_&&FILENAME&&_%DATESTR%_%TIMESTR%.log

ECHO "Started at %date% %time%" > %FILENAME%

',
		@fileFooter = '
ECHO "Finished at %date% %time%" >> %FILENAME%
PAUSE'


DECLARE crs CURSOR FOR
	SELECT		(CASE	WHEN srv_type_ro = 'Test' THEN '02_test_servers'
						WHEN srv_type_ro = 'QA' THEN '03_qa_servers'
						WHEN srv_version_major_ro = 9 THEN '06_prod_servers_2005'
						WHEN srv_version_major_ro = 10 AND srv_version_minor_ro = 0 THEN '04_prod_servers_2008'
						WHEN srv_version_major_ro = 10 AND srv_version_minor_ro > 0 THEN '05_prod_servers_2008r2'
						ELSE 'Unknown' END) ver_name,
				srv_server_name
	FROM		dbo.tblServer srv
	WHERE		srv_is_active = 1
				AND srv_is_disabled = 0
				AND srv_version_major_ro > 8
				AND srv_server_name NOT LIKE '(local)%'
				AND srv_server_name NOT LIKE 'localhost%'
				AND srv_server_name NOT LIKE 'SP1-DBA%'
				AND srv_server_name NOT IN (SELECT vnd_server_name FROM dbo.tblServerVendorManaged)
				AND ISNULL(srv_has_master_jobs, 0) = 1
				AND srv_type_ro = @report_type
	ORDER BY	(CASE	WHEN srv_type_ro = 'Test' THEN 'test_servers'
						WHEN srv_type_ro = 'QA' THEN 'qa_servers'
						WHEN srv_version_major_ro = 9 THEN 'prod_servers_2005'
						WHEN srv_version_major_ro = 10 AND srv_version_minor_ro = 0 THEN 'prod_servers_2008'
						WHEN srv_version_major_ro = 10 AND srv_version_minor_ro > 0 THEN 'prod_servers_2008r2'
						ELSE 'Unknown' END) DESC,
				srv_server_name
OPEN crs
FETCH NEXT FROM crs INTO @file_name, @server_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Force a new "file" as the output filename has changed
	IF @is_first = 0 AND @last_file_name <> @file_name
	  BEGIN
		PRINT @fileFooter
		PRINT ''
		PRINT ''
		PRINT '------------------  NEW FILE  ----------------------------'
		PRINT ''
		PRINT ''
		SET @is_first = 1
	  END

	--- On the first record of a new file, output the file header information	  
	IF @is_first = 1
	  BEGIN
		SET @is_first = 0
		PRINT REPLACE(@fileHeader, '&&FILENAME&&', @file_name)
	  END
	  
	--- Output the server detail update record
	PRINT REPLACE(@cmd, '&&SERVERNAME&&', @server_name)
	
	--- Rememeber the last filename
	SET @last_file_name = @file_name
	
	--- Fetch the next record
	FETCH NEXT FROM crs INTO @file_name, @server_name
  END
CLOSE crs
DEALLOCATE crs


PRINT @fileFooter

SET NOCOUNT ON